package dao

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"laohuo/model"
	"time"
)

func FetchWareHouses(page, pagesize uint) (model.Warehouses, error) {
	db, err := connect()
	if err != nil {
		return nil, err
	}
	defer db.Close()

	rows, err := db.Query("SELECT * FROM `warehouse` WHERE isdel=0 LIMIT ?,? ORDER BY `ctime` DESC", (page-1)*pagesize, pagesize)
	if err != nil {
		return nil, fmt.Errorf("查询失败: %v", err)
	}
	defer rows.Close()

	var whs model.Warehouses
	for rows.Next() {
		var wh model.Warehouse
		if err := rows.Scan(&wh.Id, &wh.SKUID, &wh.Num, &wh.Time); err != nil {
			continue
		}
		whs = append(whs, &wh)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("数据解析失败: %v", err)
	}
	return whs, nil
}

// FetchWareHousesWithSKU
func FetchWareHousesWithSKU(page, pagesize uint) ([]*model.WarehouseDetail, error) {
	db, err := connect()
	if err != nil {
		return nil, err
	}
	defer db.Close()

	rows, err := db.Query("SELECT w.id, w.skuid, w.num, w.time, s.name, s.wareid, s.brand, s.material, s.specs, s.color, s.ext FROM `warehouse` as w, `sku` as s WHERE w.SKUID=s.Id and w.isdel=0 ORDER BY w.Time DESC LIMIT ?,?", (page-1)*pagesize, pagesize)
	if err != nil {
		return nil, fmt.Errorf("查询失败: %v", err)
	}
	defer rows.Close()

	var whs []*model.WarehouseDetail
	for rows.Next() {
		var wh = model.WarehouseDetail{
			Warehouse: &model.Warehouse{},
			SKU:       &model.SKU{},
		}
		var ext string
		if err := rows.Scan(&wh.Warehouse.Id, &wh.SKUID, &wh.Num, &wh.Warehouse.Time, &wh.Name, &wh.WareId, &wh.Brand,
			&wh.Material, &wh.Specs, &wh.Color, &ext); err != nil {
			continue
		}
		whs = append(whs, &wh)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("数据解析失败: %v", err)
	}

	return whs, nil
}

// InputWarehouse
func InputWarehouse(skuid, num uint) (*model.Warehouse, error) {
	db, err := connect()
	if err != nil {
		return nil, err
	}
	defer db.Close()

	tx, err := db.BeginTx(context.TODO(), nil)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	var wh = model.Warehouse{}
	var isdel uint8
	err = tx.QueryRow("SELECT * FROM `warehouse` WHERE `skuid`=? FOR UPDATE", skuid).Scan(&wh.Id, &wh.SKUID, &wh.Num, &wh.Time, &isdel)
	if err != nil && err != sql.ErrNoRows {
		return nil, err
	}

	var now = time.Now().Unix()
	if _, err := tx.Exec("INSERT INTO `warehouse_bill`(`warehouseid`, `skuid`, `num`, `type`, `note`, `user`, `sign`, `feedback`, `time`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)",
		wh.Id, skuid, num, 1, "", "", "", "", now); err != nil {
		return nil, err
	}

	if wh.Id == 0 {
		res, err := tx.Exec("INSERT INTO `warehouse`(`skuid`, `num`, `time`) VALUES(?, ?, ?)", skuid, num, now)
		if err != nil {
			return nil, err
		}
		id, _ := res.LastInsertId()
		wh.SKUID = skuid
		wh.Num = num
		wh.Id = uint(id)
		wh.Time = uint(now)
	} else {
		wh.Num += num
		if _, err = tx.Exec("UPDATE `warehouse` SET `num`=? WHERE `skuid`=?", wh.Num, skuid); err != nil {
			return nil, err
		}
	}

	return &wh, tx.Commit()
}

func OutputWarehouse(skuid, num uint) (*model.Warehouse, error) {
	db, err := connect()
	if err != nil {
		return nil, err
	}
	defer db.Close()

	tx, err := db.BeginTx(context.TODO(), nil)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	var wh = model.Warehouse{}
	var isdel uint8
	err = tx.QueryRow("SELECT * FROM `warehouse` WHERE `skuid`=? FOR UPDATE", skuid).Scan(&wh.Id, &wh.SKUID, &wh.Num, &wh.Time, &isdel)
	if err != nil {
		return nil, err
	}

	if wh.Num < num {
		return nil, errors.New("no warehouse")
	}
	wh.Num -= num

	if _, err := tx.Exec("INSERT INTO `warehouse_bill`(`warehouseid`, `skuid`, `num`, `type`, `note`, `user`, `sign`, `feedback`, `time`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)",
		wh.Id, skuid, num, 2, "", "", 1, "", time.Now().Unix()); err != nil {
		return nil, err
	}

	if _, err = tx.Exec("UPDATE `warehouse` SET `num`=? WHERE `SKUID`=?", wh.Num, skuid); err != nil {
		return nil, err
	}
	return &wh, tx.Commit()
}

func CountWarehouse() (int, error) {
	db, err := connect()
	if err != nil {
		return 0, err
	}
	defer db.Close()

	count := 0
	if err := db.QueryRow("SELECT COUNT(*) FROM `warehouse` WHERE `isdel`=0").Scan(&count); err != nil {
		return 0, err
	}
	return count, nil
}
